-- @owner: zhao-pengcheng-g
-- @date: 2022/01/24
-- @testpoint:插数触发，拼接固定值，for each row，before ，insert

--step1:建表，插入数据;expect:成功
drop table if exists table_clob_0001;
create table table_clob_0001(c_id int,b_clob clob) with (storage_type=ustore)
 partition by range(c_id)
(
        partition p1 values less than(1),
        partition p2 values less than(100000),
        partition p3 values less than(maxvalue)
); 
insert into table_clob_0001 values(1,'35466');
insert into table_clob_0001 values(2,'3546213457869708234444479806');
insert into table_clob_0001 values(3,'3546213457864444479806');
insert into table_clob_0001 values(4,'35463457686543122453536');
insert into table_clob_0001 values(5,'354621345784533669708234444479806');
insert into table_clob_0001 values(6,'354621345743567378567864444479806');
insert into table_clob_0001 values(7,'100000');
insert into table_clob_0001 values(8,'101000');

--step2:创建函数，创建触发器;expect:成功
create or replace function tri_update_func_0001() returns trigger as
$$
declare
declare  cc_clob clob;
begin
 update table_clob_0001 set b_clob= b_clob || '111111111' where c_id=1;
 select b_clob into cc_clob from table_clob_0001 where c_id=1;
 raise info '%',('result:'||cc_clob);
return new;
end;
$$ language plpgsql;
/
create  trigger trigger_0001 before insert on table_clob_0001
for each row
execute procedure tri_update_func_0001();
/
--step3:插入数据，触发trigger;expect:成功
insert into table_clob_0001 values(1,'35466');
select * from table_clob_0001 where c_id=1;

--step4:环境清理;expect:成功
drop trigger trigger_0001 on table_clob_0001 cascade;
drop table  table_clob_0001 cascade;
drop function tri_update_func_0001;
